from pyparsing import Literal, CaselessLiteral, Word, Upcase, delimitedList, Optional, \
    Combine, Group, alphas, nums, alphanums, ParseException, Forward, oneOf, quotedString, \
    ZeroOrMore, restOfLine

def test( str ):
    print str,"->"
    try:
        tokens = createSQL.parseString( str )
        print "tokens = ",        tokens
        print "tokens.type =",  tokens.type
        print "tokens.path =",  tokens.path
        print "tokens.id =",  tokens.id
        print "tokens.var =", tokens.var
    except ParseException, err:
        print " "*err.loc + "^\n" + err.msg + err.pstr
        #print err
    print


# define SQL tokens
createStmt = Forward()
createToken = CaselessLiteral( "create" )

# keywords

varchar_    = Literal("varchar")
int_        = Literal("int")
smallint_   = Literal("smallint")
tinyint_    = Literal("tinyint")
decimal_    = Literal("decimal")
numeric_    = Literal("numeric")
char_       = Literal("char")
date_       = Literal("date")
varTypeName = oneOf("char varchar smallint numeric tinyint decimal date int") + Optional("(" + Word(alphanums) + ")")

ident          = Word( alphas, alphanums + "_$" ).setName("identifier")
typeName      = Upcase( delimitedList( ident, ".", combine=True ) )
typeNameList  = Group( delimitedList( typeName ) )
idName      = Upcase( delimitedList( ident, ".", combine=True ) )
idNameList  = Group( delimitedList( idName ) )
varTypeNameList = ZeroOrMore(Group(ident + varTypeName))

ident =  Word(alphas + alphanums + "/" + "'")
pathName =  Upcase( delimitedList( ident, "/", combine=True ) )
pathNameList = Group( delimitedList( pathName ) )
# define the grammar
createStmt      << ( createToken + typeNameList.setResultsName("type") +
                     Optional(pathNameList.setResultsName("path")) +
                     idNameList.setResultsName("id") +
                     varTypeNameList.setResultsName("var"))

createSQL = createStmt
createSQL.validate()

test( "CREATE DATABASE '/desired/path/to/db'  MYDB test int test1 int(30)" )
